﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Modellayer;
using System.Data.SqlClient;


namespace Dblayer
{
    public class DbSeat
    {
        SqlCommand cmd = null;
        public DbSeat()
        { 
        }

        public void createSeat(Seat seat)
        {
            try
            {
                string insert = @"insert into Seat(SeatNumber, ReservationID, ShowId, Status)
                    values ('" + seat.seatNumber + "'" + "'" + seat.reservationId + "'" + "'" + seat.showId
                               + "'" + "'" + seat.status + "')";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
        }

        public List<Seat> findSeatByShowId(int showId)
        {
            string find = @"SELECT * FROM Seat Where ShowId ='" + showId + "'";
            return buildSeatWithShowid(find);
        }

        public List<Seat> buildSeatWithShowid(string sql)
        {
            List<Seat> seats = new List<Seat>();
            System.Data.IDataReader dbReader;
            try
            {
                
                Connection.dbconn.Open();
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while(dbReader.Read())
                {
                    Seat s = new Seat();
                    s.showId = (int)dbReader["ShowId"];
                    s.seatNumber = (int)dbReader["SeatNumber"];
                    s.reservationId = (int)dbReader["ReservationID"];
                    s.status = (int)dbReader["Status"];
                    seats.Add(s);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
            return seats;
        }

        public List<Seat> findSeatByReservationsID(int ReservationId)
        {
            string find = @"SELECT * FROM Seat Where ReservationID ='" + ReservationId + "'";
            return buildSeatWithReservationsID(find);
        }

        public List<Seat> buildSeatWithReservationsID(string sql)
        {
            List<Seat> seats = null;
            System.Data.IDataReader dbReader;
            try
            {
                Connection.dbconn.Open();

                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {
                    Seat s = new Seat();
                    s.showId = (int)dbReader["ShowId"];
                    s.seatNumber = (int)dbReader["SeatNumber"];
                    s.reservationId = (int)dbReader["ReservationID"];
                    s.status = (int)dbReader["Status"];
                        
                    seats.Add(s);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
            return seats;
        }
    }
}
